package com.mbc.rmt.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;

import com.mbc.common.dao.DAO;
import com.mbc.rmt.data.CashRemittanceToAgentDataList;
import com.mbc.rmt.entities.CashRemittanceToAgent;
import com.mbc.rmt.entities.CashRemittanceToAgentRpt;

public class CashRemittanceToAgentDAOImpls extends DAO<CashRemittanceToAgent> implements CashRemittanceToAgentDAO {

	public static final String field_crtaid = "crtaid";
	public static final String field_crtano = "crtano";
	public static final String field_createddate = "createddate";
	public static final String field_createdby ="createdby";
	public static final String field_modifydate = "modifydate";
	public static final String field_receiveddate = "ReceivedDate";
	public static final String field_modifyby = "modifyby";
	public static final String field_toagentid = "toagentid";
	public static final String field_defaultbranchid = "defaultbranchid";
	public static final String field_paymentcurrencyid = "paymentcurrencyid";
	public static final String field_receivedcurrencyid ="receivedcurrencyid";
	public static final String field_rate = "rate";
	public static final String field_totalpaymentamount = "totalpaymentamount";
	public static final String field_totalreceivedamount = "totalreceivedamount";
	public static final String field_extrafees = "extrafees";
	public static final String field_netamount = "netamount";
	public static final String field_payment = "payment";
	public static final String field_bankid = "bankid";
	
	
	public CashRemittanceToAgentDAOImpls(Connection conn){
		this.conn = conn;
		this.table_name = "CashRemittanceToAgent";
	}
	
	@Override
	public void create(CashRemittanceToAgent entity) throws SQLException {
		query = insertParam(table_name, new String[] { field_crtaid, field_crtano, field_createddate, field_createdby, field_receiveddate,
				field_toagentid, field_defaultbranchid, field_paymentcurrencyid, field_receivedcurrencyid, 
				field_rate, field_totalpaymentamount, field_totalreceivedamount, field_extrafees, field_netamount, field_payment, field_bankid });
		//query = "insert into " + table_name + "(ReceiptID,ReceiptNo,CreatedDate,CreatedBy,ToAgentID,DefaultBranchID,PaymentCurrencyID,ReceivedCurrencyID,Rate,TotalPaymentAmount,TotalReceivedAmount,ExtraFees,NetAmount,Balance,BankID,Status) values( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
		pstmt = conn.prepareStatement(query);
		int i = 1;
		pstmt.setLong(i++, entity.getReceiptID());
		pstmt.setString(i++, entity.getReceiptNo());
		pstmt.setTimestamp(i++, new java.sql.Timestamp(entity.getCreatedDate().getTime()));
		if(entity.getCreatedBy()!=null)
			pstmt.setLong(i++, entity.getCreatedBy());
		else
			pstmt.setString(i++, null);
		pstmt.setTimestamp(i++, new java.sql.Timestamp(entity.getReceivedDate().getTime()));
		if(entity.getToAgentID()!=null)
			pstmt.setLong(i++, entity.getToAgentID());
		else
			pstmt.setString(i++, null);
		pstmt.setLong(i++, entity.getBranchID());
		if(entity.getPaymentCurrencyID()!=null)
			pstmt.setLong(i++, entity.getPaymentCurrencyID());
		else
			pstmt.setString(i++, null);
		if(entity.getReceivedCurrencyID()!=null)
			pstmt.setLong(i++, entity.getReceivedCurrencyID());
		else
			pstmt.setString(i++, null);
		pstmt.setDouble(i++, entity.getRate());
		pstmt.setDouble(i++, entity.getTotalPaymentAmt());
		pstmt.setDouble(i++, entity.getTotalReceivedAmt());
		pstmt.setDouble(i++, entity.getExtraFees());
		pstmt.setDouble(i++, entity.getNetAmt());
		pstmt.setDouble(i++, entity.getPayment());
		if(entity.getBankId()!=null){
			pstmt.setLong(i++, entity.getBankId());
		}
		else{
			pstmt.setString(i++, null);
		}
		pstmt.executeUpdate();
		pstmt.close();
	}

	@Override
	public void update(CashRemittanceToAgent entity) throws SQLException {
		/*query = "update "+table_name+" set CreatedDate = ?, CreatedBy= ?, ModifyBy = ?, ModifyDate = ?, ToAgentID = ?, DefaultBranchID = ?, PaymentCurrencyID = ?,ReceivedCurrencyID = ?, Rate = ?, TotalPaymentAmount = ?, TotalReceivedAmount = ?, ExtraFees = ?, NetAmount = ?, Balance = ?, BankID = ?, Status = ? where ReceiptID = "
				+ entity.getReceiptID();*/
		query = updateParam(table_name, new String[] {field_receiveddate,field_createddate, field_createdby, field_modifyby, field_modifydate,
				field_toagentid, field_defaultbranchid, field_paymentcurrencyid, field_receivedcurrencyid, field_rate, 
				field_totalpaymentamount, field_totalreceivedamount, field_extrafees, field_netamount, field_payment, 
				field_bankid })+" AND "+field_crtaid+" = "+entity.getReceiptID();
				
		pstmt = conn.prepareStatement(query);
		int i = 1;
		pstmt.setTimestamp(i++, new java.sql.Timestamp(entity.getReceivedDate().getTime()));
		pstmt.setTimestamp(i++, new Timestamp(entity.getCreatedDate().getTime()));
		if(entity.getCreatedBy()!=null)
			pstmt.setLong(i++, entity.getCreatedBy());
		else
			pstmt.setString(i++, null);
		if(entity.getModifiedBy()!=null)
			pstmt.setLong(i++, entity.getModifiedBy());
		else
			pstmt.setString(i++, null);
		pstmt.setTimestamp(i++, new Timestamp(entity.getModifiedDate().getTime()));
		if(entity.getToAgentID()!=null)
			pstmt.setLong(i++, entity.getToAgentID());
		else
			pstmt.setString(i++, null);
		pstmt.setLong(i++, entity.getBranchID());
		if(entity.getPaymentCurrencyID()!=null)
			pstmt.setLong(i++, entity.getPaymentCurrencyID());
		else
			pstmt.setString(i++, null);
		if(entity.getReceivedCurrencyID()!=null)
			pstmt.setLong(i++, entity.getReceivedCurrencyID());
		else
			pstmt.setString(i++, null);
		pstmt.setDouble(i++, entity.getRate());
		pstmt.setDouble(i++, entity.getTotalPaymentAmt());
		pstmt.setDouble(i++, entity.getTotalReceivedAmt());
		pstmt.setDouble(i++, entity.getExtraFees());
		pstmt.setDouble(i++, entity.getNetAmt());
		pstmt.setDouble(i++, entity.getPayment());
		if(entity.getBankId()!=null){
			pstmt.setLong(i++, entity.getBankId());
		}
		else{
			pstmt.setString(i++, null);
		}
		pstmt.executeUpdate();
		pstmt.close();
	}

	public void updateToAgent(CashRemittanceToAgent entity)throws SQLException{
		query = updateParam(table_name, new String[]{ field_modifyby, field_modifydate, field_payment, field_bankid})
				+" AND "+field_crtaid+" = "+entity.getReceiptID();
		pstmt = conn.prepareStatement(query);
		int i = 1 ;
		pstmt.setLong(i++, entity.getModifiedBy());
		pstmt.setTimestamp(i++, new Timestamp(entity.getModifiedDate().getTime()));
		pstmt.setDouble(i++, entity.getPayment());
		if(entity.getBankId()!=null){
			pstmt.setLong(i++, entity.getBankId());
		}
		else{
			pstmt.setString(i++, null);
		}
		pstmt.executeUpdate();
		pstmt.close();
	}
	@Override
	public void delete(long key) throws SQLException {
//		query = "update " + table_name + " set Status = " + RecordStatus.delete + " where ReceiptID = " + key;
		query = " delete from "+table_name+" where CRTAID = " + key;
		pstmt = conn.prepareStatement(query);
		pstmt.executeUpdate();
		pstmt.close();
	}

	@Override
	public CashRemittanceToAgent find(long key) throws SQLException {
		CashRemittanceToAgent l_entity = null;
		query = "select crt.*,(crt.NetAmount-crt.Payment)Balance from "+table_name + " crt where CRTAID = " + key;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if(rs.next());
			l_entity = readRecords(rs);
		rs.close();
		pstmt.close();
		return l_entity;
	}

	@Override
	public CashRemittanceToAgentDataList find(CashRemittanceToAgent cri)
			throws SQLException {
		CashRemittanceToAgentDataList l_DataList = new CashRemittanceToAgentDataList();
		ArrayList<CashRemittanceToAgent> l_entityList = new ArrayList<CashRemittanceToAgent>();
        
		/*String l_cri = " and (cra.CRTANo like N'%" + cri.getKeyword() + "%'"
				+ " or cra.ToAgentID like '%" + cri.getKeyword() 
				+ "%')" + " and cra.DefaultBranchID = " + cri.getBranchId();
		*/
		
		String l_cri = " and (" + cri.getFieldname() + " Like N'%" + cri.getKeyword() + "%') and cra.DefaultBranchID = " + cri.getBranchId(); 
		
		
       	query = "set rowcount "
				+ cri.getSize()
				+ " select * from (select  ROW_NUMBER()  over( order by cra.CRTAID desc)No, "
				+ " cra.CRTAID,cra.CRTANo,cra.CreatedDate,cra.ReceivedDate,cra.ToAgentID, a.agent_code,a.name, "
				+ " cra.PaymentCurrencyID,cp.CurrencyCode,cra.ReceivedCurrencyID, cc.CurrencyCode ReceivedCurrencyCode,cra.Rate HRate, "
		//		+ " crd.FromAgentID,aa.agent_code FromAgentCode,crd.FromBranchID,bb.BranchCode,crd.ReceiptDetailID ReceiptDetailNo,crd.AddCodeID,ac.Code,crd.Sender,crd.Receiver, "
		//		+ " crd.PaymentAmount,crd.Rate,crd.ReceivedAmount,crd.ExtraFees ExtraDetail,crd.NRC,crd.Address "
				+ " cra.TotalPaymentAmount,cra.TotalReceivedAmount,cra.ExtraFees,cra.NetAmount,cra.Payment, "
				+ " cra.BankID,ba.BankCode "
				+ " from CashRemittanceToAgent cra "   
		//		+ " left join CashRemittanceToAgentDetails crd on crd.ReceiptID = cra.ReceiptID "
				+ " left join Agent a on a.agent_id= cra.ToAgentID "
		//		+ " left join Agent aa on aa.agent_id = crd.FromAgentID "
				+ " left join Bank ba on ba.BankId = cra.BankID "  
		//		+ " left join Branch bb on bb.BranchId = crd.FromBranchID "
		//		+ " left join AddCode ac on ac.AddCodeId = crd.AddCodeID "
				+ " left join Currency cp on cp.CurrencyId=cra.PaymentCurrencyID "  
				+ " left join Currency cc on cc.CurrencyId = cra.ReceivedCurrencyID "
				+ " and cra.DefaultBranchID = " + cri.getBranchId()
				+ " where 1 = 1" + l_cri + ") " + table_name + " where No > "+cri.getIndex();
      	pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		while (rs.next()) {
			l_entityList.add(readRecord(rs));
		}
		l_DataList.setEntityList(l_entityList);
		rs.close();
		pstmt.close();
		query = "select count(CRTAID) from " + table_name + " cra left join Agent a on a.agent_id= cra.ToAgentID where 1 = 1"
				+ l_cri;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next()) {
			l_DataList.setTotalRecord(rs.getLong(1));
		}
		rs.close();
		pstmt.close();
		return l_DataList;
	}

	@Override
	protected CashRemittanceToAgent readRecord(ResultSet rs)
			throws SQLException {
		CashRemittanceToAgent l_entity = new CashRemittanceToAgent();
		l_entity.setReceiptID(rs.getLong("CRTAID"));
		l_entity.setReceiptNo(rs.getString("CRTANo"));
		l_entity.setCreatedDate(rs.getTimestamp("CreatedDate"));
//		l_entity.setModifiedDate(rs.getTimestamp("ModifyDate"));
//		l_entity.setCreatedBy(rs.getLong("CreatedBy"));
//		l_entity.setModifiedBy(rs.getLong("ModifyBy"));
		l_entity.setReceivedDate(rs.getTimestamp("ReceivedDate"));
		l_entity.setToAgentID(rs.getLong("ToAgentID"));
		l_entity.setToAgentCode(rs.getString("agent_code"));
		l_entity.setToAgentName(rs.getString("name"));
		l_entity.setPaymentCurrencyID(rs.getLong("PaymentCurrencyID"));
		l_entity.setPaymentCurrencyCode(rs.getString("CurrencyCode"));
		l_entity.setReceivedCurrencyID(rs.getLong("ReceivedCurrencyID"));
		l_entity.setReceivedCurrencyCode(rs.getString("ReceivedCurrencyCode"));
		l_entity.setRate(rs.getDouble("HRate"));
		l_entity.setBankId(rs.getLong("BankID"));
		l_entity.setBankCode(rs.getString("BankCode"));
		l_entity.setTotalPaymentAmt(rs.getDouble("TotalPaymentAmount"));
		l_entity.setTotalReceivedAmt(rs.getDouble("TotalReceivedAmount"));
		l_entity.setExtraFees(rs.getDouble("ExtraFees"));
		l_entity.setNetAmt(rs.getDouble("NetAmount"));
		l_entity.setPayment(rs.getDouble("Payment"));
		return l_entity;
	}

	@Override
	public CashRemittanceToAgent find(String receiptNo) throws SQLException {
		CashRemittanceToAgent l_entity = null;
		query = " select  ROW_NUMBER()  over( order by cra.CRTAID desc)No, "
				+ " cra.CRTAID,cra.CRTANo,cra.CreatedDate,cra.ReceivedDate,cra.ToAgentID, a.agent_code,a.name, "
				+ " cra.PaymentCurrencyID,cp.CurrencyCode,cra.ReceivedCurrencyID, cc.CurrencyCode ReceivedCurrencyCode,cra.Rate HRate, "
				+ " crd.FromAgentID,aa.agent_code FromAgentCode,crd.FromBranchID,bb.BranchCode,crd.ReceiptDetailID,crd.CRTADetailID ReceiptDetailNo,crar.ReceiptNo ReceiptNOD,crd.AddCodeID,ac.Code,crd.Sender,crd.Receiver, "
				+ " crd.PaymentAmount,crd.Rate,crd.ReceivedAmount,crd.ExtraFees ExtraDetail,crd.NRC,crd.Address "
				+ " ,cra.TotalPaymentAmount,cra.TotalReceivedAmount,cra.ExtraFees,cra.NetAmount,cra.Payment, "
				+ " cra.BankID,ba.BankCode "
				+ " from CashRemittanceToAgent cra "   
				+ " left join CashRemittanceToAgentDetails crd on crd.CRTAID = cra.CRTAID "
				+ " left join CashRemittanceByAgent_Archive crar on crar.ReceiptID = crd.CRTADetailID "
				+ " left join Agent a on a.agent_id= cra.ToAgentID "
				+ " left join Agent aa on aa.agent_id = crd.FromAgentID "
				+ " left join Bank ba on ba.BankId = cra.BankID "  
				+ " left join Branch bb on bb.BranchId = crd.FromBranchID "
				+ " left join AddCode ac on ac.AddCodeId = crd.AddCodeID "
				+ " left join Currency cp on cp.CurrencyId=cra.PaymentCurrencyID "  
				+ " left join Currency cc on cc.CurrencyId = cra.ReceivedCurrencyID " + " where cra.CRTANo = '" + receiptNo + "'";
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if(rs.next());
			l_entity = readRecord(rs);
		rs.close();
		pstmt.close();
		return l_entity;
	}

	@Override
	public CashRemittanceToAgent getCRToAgent(CashRemittanceToAgent entity)
			throws SQLException {
		CashRemittanceToAgent crtoAgent = null;
		query = "select crta.*,(crta.TotalPaymentAmount-crta.Payment)Balance "
				+ " from CashRemittanceToAgent crta "
				+ " left join CashRemittanceToAgentDetails crtad on crtad.CRTAID = crta.CRTAID "
				+ " where crta.CRTAID = " + entity.getReceiptID(); 
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if(rs.next())
			crtoAgent = readRecords(rs);
		rs.close();
		pstmt.close();
		return crtoAgent;
	}

	private CashRemittanceToAgent readRecords(ResultSet rs)throws SQLException {
		CashRemittanceToAgent l_entity = new CashRemittanceToAgent();
		l_entity.setReceiptID(rs.getLong(field_crtaid));
		l_entity.setReceiptNo(rs.getString(field_crtano));
		l_entity.setReceivedDate(rs.getTimestamp("ReceivedDate"));
		l_entity.setCreatedDate(rs.getTimestamp(field_createddate));
		l_entity.setCreatedBy(rs.getLong(field_createdby));
		l_entity.setModifiedDate(rs.getTimestamp(field_modifydate));
		l_entity.setModifiedBy(rs.getLong(field_modifyby));
		l_entity.setToAgentID(rs.getLong(field_toagentid));
		l_entity.setBranchID(rs.getLong(field_defaultbranchid));
		l_entity.setPaymentCurrencyID(rs.getLong(field_paymentcurrencyid));
		l_entity.setReceivedCurrencyID(rs.getLong(field_receivedcurrencyid));
		l_entity.setExtraFees(rs.getDouble(field_extrafees));
		l_entity.setNetAmt(rs.getDouble(field_netamount));
		l_entity.setPayment(rs.getDouble(field_payment));
		l_entity.setBankId(rs.getLong(field_bankid));
		l_entity.setBalance(rs.getDouble("Balance"));
		return l_entity;
	}
	
	@Override
	public ResultSet prepareCashRemittanceToAgentReport(
			CashRemittanceToAgentRpt info) throws SQLException {
		ResultSet l_rs;
		String l_query = "[dbo].[sp_cr_CashRemittanceToAgentRpt] ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?";
		
		CallableStatement l_pstmt = conn.prepareCall(l_query);
		CashRemittanceToAgentRptParameter(l_pstmt, info);
		l_rs = l_pstmt.executeQuery();
		return l_rs;
	}

	private static void CashRemittanceToAgentRptParameter(CallableStatement l_pstmt,
			CashRemittanceToAgentRpt info) throws SQLException{
		int i = 1;
		
		l_pstmt.setString(i++, new java.sql.Timestamp(info.getFromdate().getTime()).toString());
		l_pstmt.setString(i++, new java.sql.Timestamp(info.getTodate().getTime()).toString());
		l_pstmt.setString(i++, info.getFromreceiptNo());
		l_pstmt.setString(i++, info.getToreceiptNo());
		l_pstmt.setString(i++, info.getFromagentID());
		l_pstmt.setString(i++, info.getToagentID());
		l_pstmt.setString(i++, info.getFromtypeID());
		l_pstmt.setString(i++, info.getTotypeID());
		l_pstmt.setString(i++, info.getFromuserID());
		l_pstmt.setString(i++, info.getTouserID());
		l_pstmt.setString(i++, info.getFrombankchequeID());
		l_pstmt.setString(i++, info.getTobankchequeID());
		l_pstmt.setString(i++, info.getFromcurrencyID());
		l_pstmt.setString(i++, info.getTocurrencyID());
		l_pstmt.setString(i++, info.getFromaddcodeID());
		l_pstmt.setString(i++, info.getToaddcodeID());
		l_pstmt.setString(i++, info.getStatus());
		l_pstmt.setString(i++, info.getOrderBy());
		l_pstmt.setLong(i++, info.getBranchID());
		
	}

	@Override
	public int getTotalReceipt(long branchid, long hobranchid) throws SQLException {
		
		if(branchid == hobranchid)
			query = "select count(ReceiptDetailID) from CashRemittanceByAgentDetails_Archive crad " +
					"inner join CashRemittanceByAgent_Archive cra on cra.ReceiptID = crad.ReceiptID " +
					" where ToAgentID not in (0) and crad.[Status] is null and TransactionType = 0";
		else
			query = "select count(ReceiptDetailID) from CashRemittanceByAgentDetails_Archive crad " +
					"inner join CashRemittanceByAgent_Archive cra on cra.ReceiptID = crad.ReceiptID " +
					" where ToAgentID not in (0) and crad.[Status] is null and cra.DefaultBranchID = " + branchid;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		rs.next();
		int l_total = rs.getInt(1);
		rs.close();
		pstmt.close();
		return l_total;
	}

}
